#include "dbutil.h"

//数据库目录
const QString dbUtil::dbPath = ConfigUtil::appHome + "/data.db";
//查询group的语句
const QString dbUtil::groupListSql = "select group_name from td_group ORDER BY sort DESC, id DESC";

dbUtil::dbUtil(QObject *parent) : QObject(parent)
{}

dbUtil::~dbUtil()
{
}

QSqlDatabase dbUtil::getDbConnect(QString typeName) {
    QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE", typeName);
    //获取我的文档地址
    db.setDatabaseName(dbPath);
    db.open();
    if(!db.isOpen()) {
        QMessageBox::warning(NULL, "数据库通知", "链接数据库失败");
    }
    return db;
}

void dbUtil::distoryDB(QSqlDatabase db)
{
    try {
        if(db.isOpen()) {
            db.close();
        }
    }  catch (QSqlError e) {
        qDebug() << e.text();
    }

}

void dbUtil::initSql() {
    QSqlDatabase db = getDbConnect("init_connect");
    //初始化分组表
    QString groupSql = "create table if not exists td_group ("
                       " id INTEGER not null constraint td_group_pk primary key autoincrement, "
                       " group_name TEXT default '' not null, "
                       " create_time NUMERIC, sort int default 0 not null"
                       ")";
    QSqlQuery sqlQuery = QSqlQuery(db);
    if(!sqlQuery.exec(groupSql)) {
        QMessageBox::warning(NULL, "消息提示", "初始化分组数据失败");
    }
    QString taskSql = "create table if not exists td_task ("
                      "id INTEGER not null constraint td_todo_pk primary key autoincrement,"
                      " task_txt TEXT default '' not null,"
                      " group_id INTEGER DEFAULT 0,"
                      " create_time NUMERIC,"
                      " plan_time NUMERIC,"
                      " done_time NUMERIC,"
                      " enable_status INTEGER DEFAULT 0,"
                      " important_status INTEGER DEFAULT 0,"
                      " collection_status INTEGER DEFAULT 0"
                      ")";
    if(!sqlQuery.exec(taskSql)) {
        QMessageBox::warning(NULL, "消息提示", "初始化任务数据失败");
    }
    dbUtil::distoryDB(db);
}

void dbUtil::addGroup(QString groupName) {
    QSqlDatabase db = getDbConnect("add_group_connect");
    //初始化分组表
    QString addGroupSql = "insert into td_group (group_name, create_time, sort) values (:groupName, :createTime, :sort)";
    QSqlQuery sqlQuery = QSqlQuery(db);
    sqlQuery.prepare(addGroupSql);
    sqlQuery.bindValue(":groupName", groupName);
    sqlQuery.bindValue(":createTime", QDateTime::currentDateTime());
    sqlQuery.bindValue(":sort", 1);
    if(!sqlQuery.exec()) {
        QMessageBox::warning(NULL, "消息提示", "初始化数据失败");
    }
    dbUtil::distoryDB(db);
}

void dbUtil::delGroupByPrimaryId(int primaryId) {
    if(primaryId == 1) {
        QMessageBox::warning(nullptr, "消息提示", "默认分组不能删除");
    }
    QSqlDatabase db = getDbConnect("del_group_connect");
    //初始化分组表
    QString delGroupByIdSql = "delete from td_group where id=:primaryId";
    QSqlQuery sqlQuery = QSqlQuery(db);
    sqlQuery.prepare(delGroupByIdSql);
    sqlQuery.bindValue(":primaryId", primaryId);
    if(!sqlQuery.exec()) {
        QMessageBox::warning(NULL, "消息提示", "删除数据失败");
    }
    dbUtil::distoryDB(db);
}

void dbUtil::delTaskByGroupId(int groupId)
{
    if(groupId < 0) {
        QMessageBox::warning(NULL, "消息提示", "分组未选择");
    }
    QSqlDatabase db = getDbConnect("del_task_group_connect");
    //初始化分组表
    QString delTaskByGroupIdSql = "delete from td_task where group_id=:groupId";
    QSqlQuery sqlQuery = QSqlQuery(db);
    sqlQuery.prepare(delTaskByGroupIdSql);
    sqlQuery.bindValue(":groupId", groupId);
    if(!sqlQuery.exec()) {
        QMessageBox::warning(NULL, "消息提示", "删除数据失败");
    }
    dbUtil::distoryDB(db);
}

bool dbUtil::addTask(QString taskContext
                     , int groupId
                     , QDate taskDate
                     , int importantStatus
                     )
{
    QSqlDatabase db = getDbConnect("add_task_connect");
    //初始化分组表
    QString addTaskSql = "insert into td_task (task_txt, group_id, plan_time, create_time, important_status) values (:taskTxt, :groupId, :planTime, :createTime, :importantStatus)";
    QSqlQuery sqlQuery = QSqlQuery(db);
    sqlQuery.prepare(addTaskSql);
    sqlQuery.bindValue(":taskTxt", taskContext);
    sqlQuery.bindValue(":groupId", groupId);
    sqlQuery.bindValue(":planTime", taskDate);
    sqlQuery.bindValue(":createTime", QDateTime::currentDateTime());
    sqlQuery.bindValue(":importantStatus", importantStatus);
    if(!sqlQuery.exec()) {
        dbUtil::distoryDB(db);
        QMessageBox::warning(NULL, "消息提示", "初始化数据失败");
        return false;
    }
    dbUtil::distoryDB(db);
    return true;
}

bool dbUtil::updateTask(int taskId
                           , QString taskTxt
                           , QDate planTime
                           , QDateTime doneTime
                           , int enableStatus
                           , int importantStatus
                           , int collectionStatus
                           )
{
    if(taskId < 0)
    {
        QMessageBox::warning(nullptr, "消息提示", "任务未选择");
        return false;
    }
    QString updateTaskSql = "UPDATE td_task SET ";
    QStringList paramSqlList;
    if(!taskTxt.isNull())
    {
        paramSqlList.append(" task_txt=:taskTxt");
    }
    if(planTime.isValid())
    {
        paramSqlList.append(" plan_time=:planTime");
    }
    if(doneTime.isValid())
    {
        paramSqlList.append(" done_time=:doneTime");
    }
    if(enableStatus != NULL)
    {
        paramSqlList.append(" enable_status=:enableStatus");
    }
    if(importantStatus != NULL)
    {
        paramSqlList.append(" important_status=:importantStatus");
    }
    if(collectionStatus != NULL)
    {
        paramSqlList.append(" collection_status=:collectionStatus");
    }
    QString paramSql = paramSqlList.join(",");
    if(paramSql.isNull() || paramSql.isEmpty())
    {
        QMessageBox::warning(NULL, "消息提示", "无更新内容");
        return false;
    }
    updateTaskSql.append(paramSql).append(" WHERE id=:taskId");
    qDebug() << "更新的sql是: " << updateTaskSql;
    QSqlDatabase db = getDbConnect("update_task_connect");
    QSqlQuery sqlQuery = QSqlQuery(db);
    sqlQuery.prepare(updateTaskSql);
    sqlQuery.bindValue(":taskId", taskId);
    if(!taskTxt.isNull())
    {
        sqlQuery.bindValue(":taskTxt", taskTxt);
    }
    if(planTime.isValid())
    {
        sqlQuery.bindValue(":planTime", planTime);
    }
    if(doneTime.isValid())
    {
        sqlQuery.bindValue(":doneTime", doneTime);
    }
    if(enableStatus != NULL)
    {
        sqlQuery.bindValue(":enableStatus", enableStatus);
    }
    if(importantStatus != NULL)
    {
        sqlQuery.bindValue(":importantStatus", importantStatus);
    }
    if(collectionStatus != NULL)
    {
        sqlQuery.bindValue(":collectionStatus", collectionStatus);
    }

    if(!sqlQuery.exec()) {
        dbUtil::distoryDB(db);
        QMessageBox::warning(NULL, "消息提示", "更新数据失败");
        return false;
    }
    dbUtil::distoryDB(db);
    return true;
}

bool dbUtil::delTaskByPrimaryId(int primaryId)
{
    if(primaryId < 0)
    {
        QMessageBox::warning(nullptr, "消息提示", "任务未选择");
        return false;
    }
    QSqlDatabase db = getDbConnect("del_task_connect");
    QSqlQuery sqlQuery = QSqlQuery(db);
    QString delSql = "DELETE FROM td_task WHERE id=:taskId";
    sqlQuery.prepare(delSql);
    sqlQuery.bindValue(":taskId", primaryId);
    if(!sqlQuery.exec()) {
        dbUtil::distoryDB(db);
        QMessageBox::warning(nullptr, "消息提示", "更新数据失败");
        return false;
    }
    dbUtil::distoryDB(db);
    return true;
}

bool dbUtil::changeTaskGroup(int primaryId, int groupId) {
    if(primaryId < 0 || groupId < 0)
    {
        QMessageBox::warning(nullptr, "消息提示", "任务未选择");
        return false;
    }
    QSqlDatabase db = getDbConnect("change_task_group_connect");
    QSqlQuery sqlQuery = QSqlQuery(db);
    QString updateSql = "UPDATE td_task SET group_id=:groupId WHERE id=:taskId";
    sqlQuery.prepare(updateSql);
    sqlQuery.bindValue(":taskId", primaryId);
    sqlQuery.bindValue(":groupId", groupId);
    if(!sqlQuery.exec()) {
        dbUtil::distoryDB(db);
        QMessageBox::warning(nullptr, "消息提示", "更新数据失败");
        return false;
    }
    dbUtil::distoryDB(db);
    return true;
}

QList<ExportTaskModel> dbUtil::taskList(int id) {
    QSqlDatabase db = getDbConnect("group_excel_task_connect");
    QSqlQuery sqlQuery = QSqlQuery(db);
    QString selectSqlStr = "SELECT group_concat(task_txt, ';') AS task_txt,\n"
                           "       DATE(done_time) AS doneTime\n"
                           "FROM td_task\n"
                           "WHERE group_id = :groupId AND done_time is not null\n"
                           "GROUP BY DATE(done_time) ORDER BY done_time ASC";
    sqlQuery.prepare(selectSqlStr);
    sqlQuery.bindValue(":groupId", id);
    sqlQuery.exec();
    int index = 0;
    QList<ExportTaskModel> exportTaskModels = QList<ExportTaskModel>();
    while (sqlQuery.next()) {
        ExportTaskModel exportTaskModel = ExportTaskModel(sqlQuery.value(0).toString(), sqlQuery.value(1).toString());
        exportTaskModels.insert(index, exportTaskModel);
    }
    return exportTaskModels;
}
